package org.zizi.db.tool;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.zizi.db.AbstractEasySql;
import org.zizi.util.Closer;
import org.zizi.util.NameParser;
import org.zizi.util.PagedList;
import org.zizi.util.SqlBuilder;

public class SqlHelper
{
	private AbstractEasySql esql;
	
	/** table column list */
	private Map<String, List<String>> cache = new HashMap<String, List<String>>();
	
	public SqlHelper(AbstractEasySql esql)
	{
		this.esql = esql;
	}
	
	/** insert bean */
	public int insert(Object bean, String table, String include, String exclude) throws Exception
	{
		List<String> columns = getTableInfo(table);
		List<String> includes = (include == null || include.isEmpty()) ? null : Arrays.asList(include.toLowerCase().split(","));
		List<String> excludes = (exclude == null || exclude.isEmpty()) ? null : Arrays.asList(exclude.toLowerCase().split(","));
		
		//generate sql
		SqlBuilder sb1 = new SqlBuilder();
		SqlBuilder sb2 = new SqlBuilder();
		String flag = "";
		for (String s : columns)
		{
			s = s.toLowerCase();
			
			if (includes != null && !includes.contains(s)) continue;
			else if (excludes != null && exclude.contains(s)) continue;
			
			Field f = BeanFieldReader.getField(bean.getClass(), NameParser.parse(s));
			if (f == null) continue;	//表中存在列，但bean中不存在对应域，所以使用表中列的默认值
			
			f.setAccessible(true);
			Object value = f.get(bean);
			
			sb1.append(flag + s);
			sb2.append(flag + "?", value);
			flag = ", ";
		}
		
		if (sb1.getSql() == null || sb1.getSql().isEmpty()) return 0;	//没有可插入的域
		
		SqlBuilder insert = new SqlBuilder("insert into " + table + "(");
		insert.append(sb1).append(") values (");
		insert.append(sb2).append(")");
		
		return esql.update(insert.getSql(), insert.getParameters());
	}
	
	/** insert bean exclude id */
	public int inserti(Object bean, String table) throws Exception
	{
		return insert(bean, table, null, "id");
	}
	
	/** update bean */
	public int update(Object bean, String table, String include, String exclude, String where, Object... parameters) throws Exception
	{
		List<String> columns = getTableInfo(table);
		List<String> includes = (include == null || include.isEmpty()) ? null : Arrays.asList(include.toLowerCase().split(","));
		List<String> excludes = (exclude == null || exclude.isEmpty()) ? null : Arrays.asList(exclude.toLowerCase().split(","));
		
		SqlBuilder sb = new SqlBuilder();
		String flag = " set ";
		for (String s : columns)
		{
			s = s.toLowerCase();
			
			if (includes != null && !includes.contains(s)) continue;
			else if (excludes != null && exclude.contains(s)) continue;
			
			Field f = BeanFieldReader.getField(bean.getClass(), NameParser.parse(s));
			if (f == null) continue;	//表中存在列，但bean中不存在对应域，所以使用表中列的默认值
			
			f.setAccessible(true);
			Object value = f.get(bean);
			
			sb.append(flag + s + "=?", value);
			flag = ", ";
		}
		
		SqlBuilder update = new SqlBuilder("update " + table).append(sb).append(" where " + where, parameters);
		return esql.update(update.getSql(), update.getParameters());
	}
	
	/** update bean by id*/
	public int updatei(Object bean, String table, long id) throws Exception
	{
		return update(bean, table, null, "id", "id=?", id);
	}
	
	/** delete bean */
	public int delete(String table, String where, Object... parameters) throws Exception
	{
		SqlBuilder sb = new SqlBuilder("delete from " + table);
		if (where != null) sb.append(" where " + where, parameters);
		
		return esql.update(sb.getSql(), sb.getParameters());
	}
	
	/** delete bean by id */
	public int deletei(String table, long id) throws Exception
	{
		return delete(table, "id=?", id);
	}
	
	/** query bean */
	public <T> T query(Class<T> type, String table, String include, String exclude, String where, Object... parameters) throws Exception
	{
		SqlBuilder query = new SqlBuilder("select ");
		
		if (include != null && !include.isEmpty())
		{
			query.append(include);
		}
		else if (exclude != null && !exclude.isEmpty())
		{
			List<String> columns = getTableInfo(table);
			List<String> excludes = Arrays.asList(exclude.toLowerCase().split(","));
			
			String flag = "";
			for (String s : columns) 
			{
				s = s.toLowerCase();
				
				if (excludes.contains(s)) continue;
				query.append(flag + s);
				flag = ", ";
			}
		}
		else
		{
			query.append("*");
		}
		
		query.append(" from " + table);
		if (where != null) query.append(" where " + where, parameters);
		
		return esql.query(type, query.getSql(), query.getParameters());
	}
	
	/** query bean */
	public <T> T queryi(Class<T> type, String table, String where, Object... parameters) throws Exception
	{
		return query(type, table, null, null, where, parameters);
	}
	
	/** list table's contents */
	public <T> List<T> list(Class<T> type, String table, String include, String exclude, String order, String where, Object... parameters) throws Exception
	{
		SqlBuilder list = new SqlBuilder("select ");
		
		if (include != null && !include.isEmpty())
		{
			list.append(include);
		}
		else if (exclude != null && !exclude.isEmpty())
		{
			List<String> columns = getTableInfo(table);
			List<String> excludes = Arrays.asList(exclude.toLowerCase().split(","));
			
			String flag = "";
			for (String s : columns) 
			{
				s = s.toLowerCase();
				
				if (excludes.contains(s)) continue;
				list.append(flag + s);
				flag = ", ";
			}
		}
		else
		{
			list.append("*");
		}
		
		list.append(" from " + table);
		
		if (where != null) list.append(" where " + where, parameters);
		if (order != null) list.append(" order by " + order);
		
		return esql.list(type, list.getSql(), list.getParameters());
	}
	
	/** list table's all contents */
	public <T> List<T> listi(Class<T> type, String table, String order, String where, Object... parameters) throws Exception
	{
		return list(type, table, null, null, order, where, parameters);
	}
	
	/** page list table's contents */
	public <T> PagedList<T> page(Class<T> type, String table, int offset, int limit, String include, String exclude, String order, String where, Object... parameters) throws Exception
	{
		SqlBuilder list = new SqlBuilder("select ");
		
		if (include != null && !include.isEmpty())
		{
			list.append(include);
		}
		else if (exclude != null && !exclude.isEmpty())
		{
			List<String> columns = getTableInfo(table);
			List<String> excludes = Arrays.asList(exclude.toLowerCase().split(","));
			
			String flag = "";
			for (String s : columns) 
			{
				s = s.toLowerCase();
				
				if (excludes.contains(s)) continue;
				list.append(flag + s);
				flag = ", ";
			}
		}
		else
		{
			list.append("*");
		}
		
		list.append(" from " + table);
		
		if (where != null) list.append(" where " + where, parameters);
		if (order != null) list.append(" order by " + order);
		
		List<T> result = esql.page(type, offset, limit, list.getSql(), list.getParameters());
		int total = query(Integer.class, table, "count(*)", null, where, parameters);
		
		return new PagedList<T>(result, total);
	}
	
	/** page list table's all contents */
	public <T> PagedList<T> pagei(Class<T> type, String table, int offset, int limit, String order, String where, Object... parameters) throws Exception
	{
		return page(type, table, offset, limit, null, null, order, where, parameters);
	}
	
	///
	private List<String> getTableInfo(String table) throws Exception
	{
		List<String> info = cache.get(table);
		if (info != null) return info;
		
		String sql = esql.getTableInfoSql(table);
		Connection c = esql.getConnection();
		
		ResultSet rs = null;
		info = new ArrayList<String>();
		
		try
		{
			Statement s = c.createStatement();
			rs = s.executeQuery(sql);
			
			ResultSetMetaData meta = rs.getMetaData();
			for (int i = 0; i < meta.getColumnCount(); i++)
			{
				info.add(meta.getColumnName(i + 1));
			}
		}
		finally
		{
			Closer.close(rs);
		}
		
		cache.put(table, info);
		
		return info;
	}
}
